********Table L.1 - Mexico********

*First part (using MxFLS data)

*Load data and create differences
cd "$reploc/data/created_data"
use mex_full, clear

*Do these results for primary sample
sort pid_link mxfls
foreach var of varlist rbuc_nga grbrprovAvg_lam0co3 grbrprovSD_lam0co3 inflation{
gen `var'dif=`var'-`var'[_n-1] if mxfls==3 & pid_link==pid_link[_n-1]
}

gen coh_prov_clust=yearprov

gen primarysample1=0
replace primarysample1=1 if rbuc_ngadif!=. & grbrprovAvg_lam0co3dif!=. & grbrprovSD_lam0co3dif!=. & inflationdif!=. &  birthyr>=1925 & coh_prov_clust!=.

egen primarysample=total(primarysample1), by(pid_link)

keep if primarysample==1

*Annualized growth rate in state of residence, assume 0 in MxFLS2
order grex????

gen grexannualized=0 if mxfls==2

sort pid_link mxfls
forval yi2=2005/2007{
forval yi3=2009/2012{
ereplace grexannualized=rowmean(grex`yi2' - grex`yi3') if yearinterviewmx2[_n-1]==`yi2' & mxfls[_n-1]==2 & yearinterviewmx3==`yi3' &  mxfls==3
}
}
gen provex=ent

*Normalize
gen income_ia_use=income_ia/1000

*Generate log income
gen loginc_ia_use=log(income_ia_use)

local welfarevars loginc_ia_use unemployed

keep mxfls pid_link inflation imp_age panel married hhsize hhsize_sq Eddum* `welfarevars' grexannualized birthyr birthstate yearprov

reshape wide imp_age married hhsize hhsize_sq Eddum1 Eddum2 Eddum3 Eddum4  `welfarevars' inflation grexannualized, i(pid_link) j(mxfls)

local varlist1 imp_age married hhsize hhsize_sq Eddum1 Eddum2 Eddum3 Eddum4  `welfarevars' inflation grexannualized

foreach var in `varlist1'  {
gen `var'dif=.
replace `var'dif=`var'3-`var'2
}

gen coh_prov_clust=yearprov

*Regressions
local usei grex

cd "$reploc/results"	

local i=0
foreach var in `welfarevars' {

reg `var'dif `usei'annualizeddif marrieddif hhsizedif hhsize_sqdif Eddum*dif, vce(cluster coh_prov_clust)

if `i'==0 {
outreg2 using TableL1_Mexico.tex, replace label ctitle(`var')  keep(`usei'annualizeddif) stats(coef se)  sortvar(`usei'annualizeddif) dec(2) sdec(2)
}

if `i'>0 {
outreg2 using TableL1_Mexico.tex, append label ctitle(`var')  keep(`usei'annualizeddif) stats(coef se)  sortvar(`usei'annualizeddif) dec(2) sdec(2)
}

local i=`i'+1

}

*Second part (using Mexican population and housing censuses)

*Clean GDP growth data
cd "$reploc/data"
use Mex_prov_timeseries.dta, clear

*Winsorize province growth data at 5-95 level across all provinces
reshape long gdpgr, i(provcode) j(year)
winsor gdpgr, p(0.05) gen(gdpgr_wins)
drop gdpgr
rename gdpgr_wins gdpgr
reshape wide gdpgr, i(provcode) j(year)

order provcode gdpgr????

*Average annualized GDP growth every 5 years 
forval yr=1945/2014{
local j=`yr'-4
egen gdpgrowthannual`yr'=rowmean(gdpgr`j' - gdpgr`yr')
}

drop gdpgr????

reshape long gdp gdpgrowthannual, i(provcode) j(year)

*Keep only census years
keep if year==1990 | year==1995 | year==2000 | year==2005 | year==2010 | year==2015

rename provcode geo1_mx

tempfile growths
save "`growths'", replace

*Merge CPI information

*From 1970 to 1995 - Mexico wide CPI
cd "$reploc/data"
use Mex_cpi.dta, clear
keep if year==1970 | year==1990 | year==1995

gen id=1

tempfile cpimex
save "`cpimex'", replace

*From 2000 onwards, region-level CPI
cd "$reploc/data"
use Mex_prov_cpi.dta, clear

keep cpi12_* region*
rename cpi12_* cpi*

reshape long cpi, i(region) j(year)

rename cpi cpi_provs

tempfile cpimexprovs
save "`cpimexprovs'", replace

*Merge population
cd "$reploc/data"	
use Mex_pop.dta, clear

keep if year==1970 | year==1990 | year==1995 | year==2000 | year==2005 | year==2010 | year==2015

tempfile pop
save "`pop'", replace

*Load proportion of HHs with earthen floor in each state/year, built using the population and housing censuses of 1990, 2000, and 2010 and the population and dwelling counts of 2005. Code to build these shares available upon request.
cd "$reploc/data"	
use Mex_prov_earthfloor.dta, clear

gen region=.
replace region=1 if geo1_mx==2 | geo1_mx==3 | geo1_mx==28
replace region=2 if geo1_mx==18 | geo1_mx==25 | geo1_mx==26 
replace region=3 if geo1_mx==5 | geo1_mx==8 | geo1_mx==10 | geo1_mx==19 | geo1_mx==32
replace region=4 if geo1_mx==1 | geo1_mx==6 | geo1_mx==11 | geo1_mx==14 | geo1_mx==16 | geo1_mx==22 | geo1_mx==24
replace region=5 if geo1_mx==15 | geo1_mx==12 | geo1_mx==13 | geo1_mx==17 | geo1_mx==21 | geo1_mx==29 | geo1_mx==30
replace region=6 if geo1_mx==4 | geo1_mx==7 | geo1_mx==20 | geo1_mx==31 | geo1_mx==23 | geo1_mx==27 
replace region=7 if geo1_mx==9

merge 1:1 geo1_mx year using "`growths'", gen(_mergegrowth) keep(3)

gen id=1
merge m:1 id year using "`cpimex'", gen(_mergemexcpi)
drop id
merge m:1 region year using "`cpimexprovs'", gen(_mergemexprovscpi)
gen cpi=cpi_mex if year<2000
replace cpi=cpi_provs if year>=2000
drop if geo1_mx==.

merge 1:1 geo1_mx year using "`pop'", gen(_mergepop) 

keep if year==1990 | year==1995 | year==2000 | year==2005 | year==2010 | year==2015

*Generate clustering variable
egen year_prov_clust=group(year geo1_mx)

*Generate change in variables

sort geo1_mx year

foreach var of varlist earthfloor age cpi gdp pop woman {
gen `var'dif=`var'-`var'[_n-1] if year>1990 & geo1_mx==geo1_mx[_n-1]
}

*Regression

rename gdpgrowthannual `usei'annualizeddif

reg earthfloordif `usei'annualizeddif i.year i.geo1_mx populationdif womandif agedif 

cd "$reploc/results"	
outreg2 using TableL1_Mexico.tex, append label ctitle(earthfloor)  keep(`usei'annualizeddif) stats(coef se)  sortvar(`usei'annualizeddif) dec(2) sdec(2)
